﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;

namespace DATN.mds.ContactManagement
{
    public partial class ImportExcel : System.Web.UI.Page
    {
        private string cnnString;
        SqlConnection cnn;
        SqlCommand cmd;
        OleDbCommand ocmd;
        OleDbConnection oconn;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Session["Us_Id"] != null)
                {
                   //Do something here!
                    Lb_1.Text = "";
                    Lb_2.Text = "";
                    Session["Remind"] = null;
                }
                else
                {
                    Response.Redirect("../Account/Login.aspx");
                }
                Validate();
            }
        }

        protected void Bt_Import_Click(object sender, EventArgs e)
        {
            if ((MyFile.PostedFile != null) && (MyFile.PostedFile.ContentLength > 0))
            {
                String ext = Path.GetExtension(MyFile.FileName);
                string fn = System.IO.Path.GetFileName(MyFile.PostedFile.FileName);
                string SaveLocation = Server.MapPath("..\\UploadFile") + "\\" + fn;
                try
                {
                    if ((MyFile.HasFile == false) || (MyFile.PostedFile.ContentLength > 1000000))
                    {
                        Lb_1.Visible = true;
                        Lb_1.Text = "Vui lòng chọn ảnh có kích thước dưới 1MB";
                    }
                    else
                    {
                        if (ext == ".xls" || ext == ".xlsx")
                        {
                                MyFile.PostedFile.SaveAs(SaveLocation);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Lb_2.Text = "Lỗi Upload File: " + ex.Message;
                }
            }
            else
            {
                Lb_2.Text = "Chưa chọn file tải lên!.";
            }

            //Đọc file Excel và đổ vào database:
            ReadExcel();
        }

        public void ReadExcel()
        {
            string Extension = Path.GetExtension(MyFile.PostedFile.FileName);
            string path = MyFile.PostedFile.FileName;
            string filePath = Path.GetFullPath(MyFile.FileName);

            String strConn = @"Provider = Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + Server.MapPath("..\\UploadFile") + "\\" +
                                 MyFile.FileName + @";Extended Properties='Excel 12.0; HDR=Yes;'";

            String strConn1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("..\\UploadFile") + "\\" +
                                 MyFile.FileName + "; Extended Properties=Excel 8.0";
            string conStr = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = strConn1;
                    break;
                case ".xlsx": //Excel 07
                    conStr = strConn;
                    break;
            }
            try
            {
                oconn = new OleDbConnection(conStr);
                ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                oconn.Open();  //Here [Sheet1$] is the name of the sheet in the Excel file where the data is present
                OleDbDataReader odr = ocmd.ExecuteReader();
                string EmpFirstName = "";
                string EmpLastName = "";
                string EmpBirthday = "";
                string EmpSex = "";
                string EmpPhone = "";
                string EmpMail = "";
                string EmpAddress = "";
                string EmpCity = "";
                string EmpCompany = "";
                string EmpMaritalStatus = "";
                string EmpJob = "";
                string EmpSalary = "";
                string EmpHabit = "";
             
                while (odr.Read())
                {
                    EmpFirstName = valid(odr, 0);
                    EmpLastName = valid(odr,1);
                    EmpBirthday = valid(odr, 2);
                    EmpSex = valid(odr, 3);
                    EmpPhone = valid(odr, 4);
                    EmpMail = valid(odr, 5);
                    EmpAddress = valid(odr, 6);
                    EmpCity = valid(odr, 7);
                    EmpCompany = valid(odr, 8);
                    EmpMaritalStatus = valid(odr, 9);
                    EmpJob = valid(odr, 10);
                    EmpSalary = valid(odr, 11);
                    EmpHabit = valid(odr, 12);

                   // Lb_1.Text = EmpFirstName + EmpLastName + EmpBirthday + EmpSex + EmpPhone + EmpMail + 
                   // EmpAddress + EmpCity + EmpCompany + EmpMaritalStatus + EmpJob + EmpSalary + EmpHabit;
                    insertdataintosql(EmpFirstName, EmpLastName, EmpBirthday, EmpSex, EmpPhone,
                        EmpMail, EmpAddress, EmpCity, EmpCompany, EmpMaritalStatus, EmpJob, EmpSalary, EmpHabit);
                }
                
                Lb_1.Text = "Thành Công";
                oconn.Close();
                Response.Redirect("../mds.ContactManagement/IndexCM.aspx");
            }
            catch (Exception ex)
            {
                oconn.Close();
                Lb_1.Text = "Lỗi Khi Upload File : " + ex;
            }
        }


        //Bắt Validation Từ Chuỗi của File Excel
        protected string valid(OleDbDataReader myreader, int stval)
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return Convert.ToString(0);
        }

        //Insert Vào database
        public void insertdataintosql(string EmpFirstName, string EmpLastName, string EmpBirthday, string EmpSex, 
            string EmpPhone, string EmpMail, string EmpAddress, string EmpCity, string EmpCompany,
            string EmpMaritalStatus, string EmpJob, string EmpSalary, string EmpHabit)
            {
            try
            {
                int us_id = Convert.ToInt16(Session["Us_Id"].ToString());
                cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ToString();
                cnn = new SqlConnection(cnnString);
                cnn.Open();
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = cnn;
                cmd.CommandText = "INSERT INTO [dbo].[Customers] ([FirstName], [LastName], [Birthday]"
                +", [Sex], [Phone], [Mail], [Address], [City], [Company], [MaritalStatus],"
                +"[Job], [Salary], [Habit], [DateCreate], [Us_Id]) "
                +"VALUES (@FirstName, @LastName, @Birthday, @Sex, @Phone, @Mail,"
                +"@Address, @City, @Company, @MaritalStatus, @Job, @Salary, @Habit, @DateCreate, @Us_Id)";


                cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = EmpFirstName;
                cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = EmpLastName;
                cmd.Parameters.Add("@Birthday", SqlDbType.Date).Value = EmpBirthday;
                cmd.Parameters.Add("@Sex", SqlDbType.NVarChar, 20).Value = EmpSex;
                cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 16).Value = EmpPhone;
                cmd.Parameters.Add("@Mail", SqlDbType.NVarChar, 50).Value = EmpMail ;
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 150).Value = EmpAddress ;
                cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = EmpCity;
                cmd.Parameters.Add("@Company", SqlDbType.NVarChar, 50).Value = EmpCompany;
                cmd.Parameters.Add("@MaritalStatus", SqlDbType.NVarChar, 20).Value = EmpMaritalStatus;
                cmd.Parameters.Add("@Job", SqlDbType.NVarChar, 50).Value = EmpJob;
                cmd.Parameters.Add("@Salary", SqlDbType.Int).Value = EmpSalary;
                cmd.Parameters.Add("@Habit", SqlDbType.NText).Value = EmpHabit;
                cmd.Parameters.Add("@DateCreate", SqlDbType.DateTime).Value = DateTime.Now.ToString();
                cmd.Parameters.Add("@Us_Id", SqlDbType.Int).Value = us_id;
                

                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cnn.Close();
            }
            catch (Exception ex)
            {
                Lb_2.Text = "Lỗi Với Dữ liệu trong File Excel" + ex ;
                cnn.Close();
            }
        }
    }
}